﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using Common.Utilities;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;

namespace Workflows.Components.Entities.Collapses
{
    public delegate void PopulatingRow(int rowNumber, int totalRows);

    /// <summary>
    /// 
    /// </summary>
    public class ExcelTableWithGroupsUtil
    {
        public static event PopulatingRow OnPopulatingRow;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rangeFrom"></param>
        /// <param name="colHeaders"></param>
        /// <param name="detailTableField_ColHeader_Mappings"></param>
        /// <param name="masterTableField_ColHeader_Mappings"></param>
        /// <param name="parentKeyFieldName"></param>
        /// <param name="childKeyFieldName"></param>
        /// <param name="masterTable"></param>
        /// <param name="detailTable"></param>
        /// <param name="showMasterContentBelowDetailContent"></param>
        public static void FillTableWithGroup(
            Worksheet sheet, string rangeFrom,
            List<string> colHeaders,
            Dictionary<string, string> detailTableField_ColHeader_Mappings,
            Dictionary<string, string> masterTableField_ColHeader_Mappings,
            string parentKeyFieldName, string childKeyFieldName,
            DataTable masterTable, DataTable detailTable,
            bool showMasterContentBelowDetailContent)
        {
            sheet.Outline.ShowLevels(1, Type.Missing);
            sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;
            if (showMasterContentBelowDetailContent)
            {
                sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryBelow;
            }
            sheet.Outline.AutomaticStyles = false;

            int startingColIndex = WorksheetHelper.GetColumnIndex(rangeFrom);
            int startingRowIndex = WorksheetHelper.GetRowIndex(rangeFrom);
            // column headers
            Dictionary<string, int> colHeader_ColIndex_Mappings = new Dictionary<string, int>();
            for (int i = 0; i < colHeaders.Count; i++)
            {
                Range rng = WorksheetHelper.GetCell(sheet, startingColIndex + i, startingRowIndex);
                rng.Value2 = colHeaders[i];
                colHeader_ColIndex_Mappings.Add(colHeaders[i], startingColIndex + i);
            }

            // master table 
            int rowIndex = startingRowIndex;
            for (int i = 0; i < masterTable.Rows.Count; i++)
            {
                rowIndex++;
                int pkValue = (int)masterTable.Rows[i][parentKeyFieldName];
                for (int k = 0; k < masterTable.Columns.Count; k++)
                {
                    string masterColName = masterTable.Columns[k].ColumnName;
                    string masterColHeader = masterTableField_ColHeader_Mappings[masterColName];
                    int masterColIndex = colHeader_ColIndex_Mappings[masterColHeader];
                    Range rng = WorksheetHelper.GetCell(sheet, masterColIndex, rowIndex);
                    rng.Value2 = masterTable.Rows[i][k];
                }

                // detail rows
                DataRow[] drDetails = detailTable.Select(string.Format("{0}={1}", childKeyFieldName, pkValue));
                if (drDetails != null && drDetails.Length > 0)
                {
                    int groupFromIdx = rowIndex + 1;
                    int groupToIdx = 0;
                    for (int m = 0; m < drDetails.Length; m++)
                    {
                        rowIndex++;
                        for (int n = 0; n < detailTable.Columns.Count; n++)
                        {
                            string detailColName = detailTable.Columns[n].ColumnName;
                            string detailColHeader = detailTableField_ColHeader_Mappings[detailColName];
                            int detailColIndex = colHeader_ColIndex_Mappings[detailColHeader];
                            Range rng = WorksheetHelper.GetCell(sheet, detailColIndex, rowIndex);
                            rng.Value2 = drDetails[m][n];
                        }
                    }
                    groupToIdx = rowIndex;
                    Range groupRng = (Range)sheet.Rows[string.Format("{0}:{1}", groupFromIdx, groupToIdx), Type.Missing];
                    groupRng.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="colHeaders"></param>
        /// <param name="detailTableField_ColHeader_Mappings"></param>
        /// <param name="masterTableField_ColHeader_Mappings"></param>
        /// <param name="parentKeyFieldName"></param>
        /// <param name="childKeyFieldName"></param>
        /// <param name="masterID_DetailIDs_Mappings"></param>
        /// <param name="masterTable"></param>
        /// <param name="detailTable"></param>
        /// <param name="showMasterContentBelowDetailContent"></param>
        public static void FillTableWithGroup(
            List<string> colHeaders,
            Dictionary<string, string> detailTableField_ColHeader_Mappings,
            Dictionary<string, string> masterTableField_ColHeader_Mappings,
            string parentKeyFieldName, string childKeyFieldName,
            Dictionary<int,List<int>> masterID_DetailIDs_Mappings,
            DataTable masterTable, DataTable detailTable,
            bool showMasterContentBelowDetailContent)
        {
            Application excelApp = new Application();
            excelApp.Visible = true;
            Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
            excelApp.DisplayAlerts = false;
            Worksheet sheet = (Worksheet) excelWorkbook.ActiveSheet;
            sheet.Activate();
            string rangeFrom = "A1";

            sheet.Outline.ShowLevels(1, Type.Missing);
            sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;
            if (showMasterContentBelowDetailContent)
            {
                sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryBelow;
            }
            sheet.Outline.AutomaticStyles = false;

            int startingColIndex = WorksheetHelper.GetColumnIndex(rangeFrom);
            int startingRowIndex = WorksheetHelper.GetRowIndex(rangeFrom);
            // column headers
            Dictionary<string, int> colHeader_ColIndex_Mappings = new Dictionary<string, int>();
            for (int i = 0; i < colHeaders.Count; i++)
            {
                Range rng = WorksheetHelper.GetCell(sheet, startingColIndex + i, startingRowIndex);
                rng.Value2 = colHeaders[i];
                colHeader_ColIndex_Mappings.Add(colHeaders[i], startingColIndex + i);
            }
            foreach (string colHeader in colHeaders)
            {
                int colIndex = colHeader_ColIndex_Mappings[colHeader];
                Range hdrRange = WorksheetHelper.GetCell(sheet, colIndex, startingRowIndex);
                hdrRange.Font.Bold = true;
                hdrRange.Interior.Color = System.Drawing.Color.Silver.ToArgb();
                hdrRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                hdrRange.Borders[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb();

                SqlDbType dbType = SqlDbType.VarChar;
                if (masterTableField_ColHeader_Mappings.ContainsKey(colHeader))
                {
                    dbType = FieldDataType.ToDbType(masterTable.Columns[colHeader].DataType.ToString());
                }
                else if (detailTableField_ColHeader_Mappings.ContainsKey(colHeader))
                {
                    dbType = FieldDataType.ToDbType(detailTable.Columns[colHeader].DataType.ToString());
                }
                if (FieldDataType.IsNumber(dbType))
                {
                }
                else if (FieldDataType.IsDateTime(dbType))
                {
                    WorksheetHelper.FormatColumn(sheet, colIndex, "mm/dd/yyyy");
                }
                else
                {
                    WorksheetHelper.FormatColumnText(sheet, colIndex);
                }
            }

            // master table 
            int rowIndex = startingRowIndex;
            for (int i = 0; i < masterTable.Rows.Count; i++)
            {
                rowIndex++;
                int pkValue = (int)masterTable.Rows[i][parentKeyFieldName];
                for (int k = 0; k < masterTable.Columns.Count; k++)
                {
                    string masterColName = masterTable.Columns[k].ColumnName;
                    if (masterTableField_ColHeader_Mappings.ContainsKey(masterColName))
                    {
                        string masterColHeader = masterTableField_ColHeader_Mappings[masterColName];
                        int masterColIndex = colHeader_ColIndex_Mappings[masterColHeader];
                        Range rng = WorksheetHelper.GetCell(sheet, masterColIndex, rowIndex);
                        rng.Value2 = masterTable.Rows[i][k];
                    }
                }
                foreach(string colHeader in colHeaders)
                {
                    int colIndex = colHeader_ColIndex_Mappings[colHeader];
                    Range hdrRange = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                    hdrRange.Font.Bold = true;
                    hdrRange.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
                    hdrRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                    hdrRange.Borders[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb();
                }
                

                // detail rows
                List<int> detailIDs = masterID_DetailIDs_Mappings[pkValue];
                DataRow[] drDetails = detailTable.Select(
                    string.Format("{0} in ({1})",
                                  childKeyFieldName,
                                  IntUtil.ToString(detailIDs.ToArray())));
                if (drDetails != null && drDetails.Length > 0)
                {
                    int groupFromIdx = rowIndex + 1;
                    int groupToIdx = 0;
                    for (int m = 0; m < drDetails.Length; m++)
                    {
                        rowIndex++;
                        for (int n = 0; n < detailTable.Columns.Count; n++)
                        {
                            string detailColName = detailTable.Columns[n].ColumnName;
                            if (detailTableField_ColHeader_Mappings.ContainsKey(detailColName))
                            {
                                string detailColHeader = detailTableField_ColHeader_Mappings[detailColName];
                                int detailColIndex = colHeader_ColIndex_Mappings[detailColHeader];
                                Range rng = WorksheetHelper.GetCell(sheet, detailColIndex, rowIndex);
                                rng.Value2 = drDetails[m][n];

                                if (masterTableField_ColHeader_Mappings.ContainsKey(detailColName))
                                {
                                    if (drDetails[m][n] != null &&
                                        drDetails[m][n].ToString().Length > 0 &&
                                        drDetails[m][n].ToString().ToUpper() != masterTable.Rows[i][detailColName].ToString().ToUpper())
                                    {
                                        rng.Interior.Color =
                                            System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow);
                                    }
                                }
                            }
                        }
                    }
                    groupToIdx = rowIndex;
                    Range groupRng = (Range)sheet.Rows[string.Format("{0}:{1}", groupFromIdx, groupToIdx), Type.Missing];
                    groupRng.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="colHeaders"></param>
        /// <param name="detailTableField_ColHeader_Mappings"></param>
        /// <param name="masterTableField_ColHeader_Mappings"></param>
        /// <param name="parentKeyFieldName"></param>
        /// <param name="childKeyFieldName"></param>
        /// <param name="masterID_DetailIDs_Mappings"></param>
        /// <param name="masterTable"></param>
        /// <param name="detailTable"></param>
        /// <param name="showMasterContentBelowDetailContent"></param>
        public static void FillTableWithGroup(
            string excelFilePath, List<string> colHeaders,
            Dictionary<string, string> detailTableField_ColHeader_Mappings,
            Dictionary<string, string> masterTableField_ColHeader_Mappings,
            string parentKeyFieldName, string childKeyFieldName,
            Dictionary<int, List<int>> masterID_DetailIDs_Mappings,
            DataTable masterTable, DataTable detailTable,
            bool showMasterContentBelowDetailContent)
        {
            Application excelApp = new Application();
            excelApp.Visible = false;
            if(File.Exists(excelFilePath))
            {
                File.Delete(excelFilePath);
            }
            try
            {
                Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
                excelApp.DisplayAlerts = false;
                Worksheet sheet = (Worksheet)excelWorkbook.ActiveSheet;
                sheet.Activate();
                string rangeFrom = "A1";

                sheet.Outline.ShowLevels(1, Type.Missing);
                sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;
                if (showMasterContentBelowDetailContent)
                {
                    sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryBelow;
                }
                sheet.Outline.AutomaticStyles = false;

                int startingColIndex = WorksheetHelper.GetColumnIndex(rangeFrom);
                int startingRowIndex = WorksheetHelper.GetRowIndex(rangeFrom);
                // column headers
                Dictionary<string, int> colHeader_ColIndex_Mappings = new Dictionary<string, int>();
                for (int i = 0; i < colHeaders.Count; i++)
                {
                    Range rng = WorksheetHelper.GetCell(sheet, startingColIndex + i, startingRowIndex);
                    rng.Value2 = colHeaders[i];
                    colHeader_ColIndex_Mappings.Add(colHeaders[i], startingColIndex + i);
                }
                foreach (string colHeader in colHeaders)
                {
                    int colIndex = colHeader_ColIndex_Mappings[colHeader];
                    Range hdrRange = WorksheetHelper.GetCell(sheet, colIndex, startingRowIndex);
                    hdrRange.Font.Bold = true;
                    hdrRange.Interior.Color = System.Drawing.Color.Silver.ToArgb();
                    hdrRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                    hdrRange.Borders[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb();
                    SqlDbType dbType = SqlDbType.VarChar;
                    if(masterTableField_ColHeader_Mappings.ContainsKey(colHeader))
                    {
                        dbType = FieldDataType.ToDbType(masterTable.Columns[colHeader].DataType.ToString());
                    }
                    else if(detailTableField_ColHeader_Mappings.ContainsKey(colHeader))
                    {
                        dbType = FieldDataType.ToDbType(detailTable.Columns[colHeader].DataType.ToString());
                    }
                    if(FieldDataType.IsNumber(dbType))
                    {
                    }
                    else if(FieldDataType.IsDateTime(dbType))
                    {
                        WorksheetHelper.FormatColumn(sheet, colIndex, "mm/dd/yyyy");
                    }
                    else
                    {
                        WorksheetHelper.FormatColumnText(sheet,colIndex);
                    }
                }

                // master table 
                int rowIndex = startingRowIndex;
                for (int i = 0; i < masterTable.Rows.Count; i++)
                {
                    rowIndex++;
                    int pkValue = (int)masterTable.Rows[i][parentKeyFieldName];
                    for (int k = 0; k < masterTable.Columns.Count; k++)
                    {
                        string masterColName = masterTable.Columns[k].ColumnName;
                        if (masterTableField_ColHeader_Mappings.ContainsKey(masterColName))
                        {
                            string masterColHeader = masterTableField_ColHeader_Mappings[masterColName];
                            int masterColIndex = colHeader_ColIndex_Mappings[masterColHeader];
                            Range rng = WorksheetHelper.GetCell(sheet, masterColIndex, rowIndex);
                            rng.Value2 = masterTable.Rows[i][k];
                        }
                    }
                    foreach (string colHeader in colHeaders)
                    {
                        int colIndex = colHeader_ColIndex_Mappings[colHeader];
                        Range hdrRange = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                        hdrRange.Font.Italic = true;
                        hdrRange.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
                        hdrRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                        hdrRange.Borders[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb();
                    }

                    // detail rows
                    List<int> detailIDs = masterID_DetailIDs_Mappings[pkValue];
                    DataRow[] drDetails = detailTable.Select(
                        string.Format("{0} in ({1})",
                                      childKeyFieldName,
                                      IntUtil.ToString(detailIDs.ToArray())));
                    if (drDetails != null && drDetails.Length > 0)
                    {
                        int groupFromIdx = rowIndex + 1;
                        int groupToIdx = 0;
                        for (int m = 0; m < drDetails.Length; m++)
                        {
                            rowIndex++;
                            for (int n = 0; n < detailTable.Columns.Count; n++)
                            {
                                string detailColName = detailTable.Columns[n].ColumnName;
                                if (detailTableField_ColHeader_Mappings.ContainsKey(detailColName))
                                {
                                    string detailColHeader = detailTableField_ColHeader_Mappings[detailColName];
                                    int detailColIndex = colHeader_ColIndex_Mappings[detailColHeader];
                                    Range rng = WorksheetHelper.GetCell(sheet, detailColIndex, rowIndex);
                                    rng.Value2 = drDetails[m][n];

                                    if (masterTableField_ColHeader_Mappings.ContainsKey(detailColName))
                                    {
                                        if (drDetails[m][n] != null && 
                                            drDetails[m][n].ToString().Length>0 && 
                                            drDetails[m][n].ToString().ToUpper() != masterTable.Rows[i][detailColName].ToString().ToUpper())
                                        {
                                            rng.Interior.Color =
                                                System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow);
                                        }
                                    }
                                }

                                
                            }
                        }
                        groupToIdx = rowIndex;
                        Range groupRng = (Range)sheet.Rows[string.Format("{0}:{1}", groupFromIdx, groupToIdx), Type.Missing];
                        groupRng.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    }

                    if (OnPopulatingRow != null && (i+1) % 10 == 0)
                    {
                        OnPopulatingRow((i + 1), masterTable.Rows.Count);
                    }
                }

                excelWorkbook.SaveAs(excelFilePath, XlFileFormat.xlOpenXMLWorkbook,
                                     Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                     XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
                                     Type.Missing, Type.Missing, Type.Missing);
            }
            catch(Exception ex)
            {
                throw new Exception("Unable to generate excel report: " + ex.ToString());
            }
            finally
            {
                excelApp.Quit();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="colHeaders"></param>
        /// <param name="detailTableField_ColHeader_Mappings"></param>
        /// <param name="masterTableField_ColHeader_Mappings"></param>
        /// <param name="parentKeyFieldName"></param>
        /// <param name="childKeyFieldName"></param>
        /// <param name="masterTable"></param>
        /// <param name="detailTable"></param>
        /// <param name="showMasterContentBelowDetailContent"></param>
        public static void FillTableWithGroup(
            string excelFilePath, List<string> colHeaders,
            Dictionary<string, string> detailTableField_ColHeader_Mappings,
            Dictionary<string, string> masterTableField_ColHeader_Mappings,
            string parentKeyFieldName, string childKeyFieldName,
            DataTable masterTable, DataTable detailTable,
            bool showMasterContentBelowDetailContent)
        {
            Application excelApp = new Application();
            excelApp.Visible = false;
            if (File.Exists(excelFilePath))
            {
                File.Delete(excelFilePath);
            }
            try
            {
                Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
                excelApp.DisplayAlerts = false;
                Worksheet sheet = (Worksheet)excelWorkbook.ActiveSheet;
                sheet.Activate();
                string rangeFrom = "A1";

                sheet.Outline.ShowLevels(1, Type.Missing);
                sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove;
                if (showMasterContentBelowDetailContent)
                {
                    sheet.Outline.SummaryRow = XlSummaryRow.xlSummaryBelow;
                }
                sheet.Outline.AutomaticStyles = false;

                int startingColIndex = WorksheetHelper.GetColumnIndex(rangeFrom);
                int startingRowIndex = WorksheetHelper.GetRowIndex(rangeFrom);
                // column headers
                Dictionary<string, int> colHeader_ColIndex_Mappings = new Dictionary<string, int>();
                for (int i = 0; i < colHeaders.Count; i++)
                {
                    Range rng = WorksheetHelper.GetCell(sheet, startingColIndex + i, startingRowIndex);
                    rng.Value2 = colHeaders[i];
                    colHeader_ColIndex_Mappings.Add(colHeaders[i], startingColIndex + i);
                }
                foreach (string colHeader in colHeaders)
                {
                    int colIndex = colHeader_ColIndex_Mappings[colHeader];
                    Range hdrRange = WorksheetHelper.GetCell(sheet, colIndex, startingRowIndex);
                    hdrRange.Font.Bold = true;
                    hdrRange.Interior.Color = System.Drawing.Color.Silver.ToArgb();
                    hdrRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                    hdrRange.Borders[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb();
                    SqlDbType dbType = SqlDbType.VarChar;
                    if (masterTableField_ColHeader_Mappings.ContainsKey(colHeader))
                    {
                        dbType = FieldDataType.ToDbType(masterTable.Columns[colHeader].DataType.ToString());
                    }
                    else if (detailTableField_ColHeader_Mappings.ContainsKey(colHeader))
                    {
                        dbType = FieldDataType.ToDbType(detailTable.Columns[colHeader].DataType.ToString());
                    }
                    if (FieldDataType.IsNumber(dbType))
                    {
                    }
                    else if (FieldDataType.IsDateTime(dbType))
                    {
                        WorksheetHelper.FormatColumn(sheet, colIndex, "mm/dd/yyyy");
                    }
                    else
                    {
                        WorksheetHelper.FormatColumnText(sheet, colIndex);
                    }
                }

                // master table 
                int rowIndex = startingRowIndex;
                for (int i = 0; i < masterTable.Rows.Count; i++)
                {
                    rowIndex++;
                    int pkValue = (int)masterTable.Rows[i][parentKeyFieldName];
                    for (int k = 0; k < masterTable.Columns.Count; k++)
                    {
                        string masterColName = masterTable.Columns[k].ColumnName;
                        if (masterTableField_ColHeader_Mappings.ContainsKey(masterColName))
                        {
                            string masterColHeader = masterTableField_ColHeader_Mappings[masterColName];
                            int masterColIndex = colHeader_ColIndex_Mappings[masterColHeader];
                            Range rng = WorksheetHelper.GetCell(sheet, masterColIndex, rowIndex);
                            rng.Value2 = masterTable.Rows[i][k];
                        }
                    }
                    foreach (string colHeader in colHeaders)
                    {
                        int colIndex = colHeader_ColIndex_Mappings[colHeader];
                        Range hdrRange = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                        hdrRange.Font.Italic = true;
                        hdrRange.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
                        hdrRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                        hdrRange.Borders[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb();
                    }

                    // detail rows
                    DataRow[] drDetails = detailTable.Select(
                        string.Format("{0} = {1}", childKeyFieldName, pkValue));
                    if (drDetails != null && drDetails.Length > 0)
                    {
                        int groupFromIdx = rowIndex + 1;
                        int groupToIdx = 0;
                        for (int m = 0; m < drDetails.Length; m++)
                        {
                            rowIndex++;
                            for (int n = 0; n < detailTable.Columns.Count; n++)
                            {
                                string detailColName = detailTable.Columns[n].ColumnName;
                                if (detailTableField_ColHeader_Mappings.ContainsKey(detailColName))
                                {
                                    string detailColHeader = detailTableField_ColHeader_Mappings[detailColName];
                                    int detailColIndex = colHeader_ColIndex_Mappings[detailColHeader];
                                    Range rng = WorksheetHelper.GetCell(sheet, detailColIndex, rowIndex);
                                    rng.Value2 = drDetails[m][n];

                                    if (masterTableField_ColHeader_Mappings.ContainsKey(detailColName))
                                    {
                                        if (drDetails[m][n] != null &&
                                            drDetails[m][n].ToString().Length > 0 &&
                                            drDetails[m][n].ToString().ToUpper() != masterTable.Rows[i][detailColName].ToString().ToUpper())
                                        {
                                            rng.Interior.Color =
                                                System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow);
                                        }
                                    }
                                }


                            }
                        }
                        groupToIdx = rowIndex;
                        Range groupRng = (Range)sheet.Rows[string.Format("{0}:{1}", groupFromIdx, groupToIdx), Type.Missing];
                        groupRng.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    }

                    if (OnPopulatingRow != null && (i + 1) % 10 == 0)
                    {
                        OnPopulatingRow((i + 1), masterTable.Rows.Count);
                    }
                }

                excelWorkbook.SaveAs(excelFilePath, XlFileFormat.xlOpenXMLWorkbook,
                                     Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                     XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
                                     Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to generate excel report: " + ex.ToString());
            }
            finally
            {
                excelApp.Quit();
            }
        }
    }
}
